package com.clouds.wms.dao;

import java.util.List;

import javax.annotation.Resource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import com.clouds.wms.bean.Cmsmb;
import com.clouds.wms.exception.DatabaseException;
import com.clouds.wms.util.Function;

@Repository("cmsmbDao")
public class CmsmbDaoImpl implements CmsmbDao {

	private Log log = LogFactory.getLog(getClass());
	
	@Resource(name="namedParameterJdbcTemplate")
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	
	@Override
	public Cmsmb getCmsmbByMbaa(String mbaa) throws DatabaseException {
		String sql = "SELECT * FROM cmsmb WHERE mbaa=?";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().queryForObject(sql, new Object[]{mbaa}, new BeanPropertyRowMapper<Cmsmb>(Cmsmb.class));
		} catch (EmptyResultDataAccessException e) {
			log.info("没有数据");
			return null;
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public int getCmsmbCount(String mbaa, String mbab) throws DatabaseException {
		String sql = "SELECT COUNT(*) FROM cmsmb WHERE 1=1";
		if(mbaa != null && !"".equals(mbaa)) {
			sql += " AND mbaa LIKE '%" + Function.formatSQLString(mbaa) + "%'";
		}
		if(mbab != null && !"".equals(mbab)) {
			sql += " AND mbab LIKE '%" + Function.formatSQLString(mbab) + "%'";
		}
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().queryForInt(sql);
		} catch (EmptyResultDataAccessException e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public List<Cmsmb> getCmsmbList(String mbaa, String mbab, long offset, long pageSize) throws DatabaseException {
		String sql = "SELECT * FROM cmsmb WHERE 1=1";
		if(mbaa != null && !"".equals(mbaa)) {
			sql += " AND mbaa LIKE '%" + Function.formatSQLString(mbaa) + "%'";
		}
		if(mbab != null && !"".equals(mbab)) {
			sql += " AND mbab LIKE '%" + Function.formatSQLString(mbab) + "%'";
		}
		sql += " ORDER BY mbaa DESC";
		if(offset != 0 || pageSize != 0) {
			sql +=" limit " + offset + "," + pageSize;
		}
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().query(sql.toString(), new BeanPropertyRowMapper<Cmsmb>(Cmsmb.class));
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public int saveCmsmb(Cmsmb cmsmb) throws DatabaseException {
		String sql = "INSERT INTO cmsmb (mbaa, mbab, mbac) VALUES (:mbaa, :mbab, :mbac)";
		try {
			SqlParameterSource param = new BeanPropertySqlParameterSource(cmsmb);
			return namedParameterJdbcTemplate.update(sql, param);
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public int updateCmsmb(Cmsmb cmsmb) throws DatabaseException {
		String sql = "UPDATE cmsmb SET mbab=:mbab, mbac=:mbac WHERE mbaa=:mbaa";
		try {
			SqlParameterSource param = new BeanPropertySqlParameterSource(cmsmb);
			return namedParameterJdbcTemplate.update(sql, param);
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public int deleteCmsmbByMbaa(String mbaa) throws DatabaseException {
		String sql = "DELETE FROM cmsmb WHERE mbaa=?";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().update(sql, new Object[]{mbaa});
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

	@Override
	public List<Cmsmb> getAllCmsmbList() throws DatabaseException {
		String sql = "SELECT * FROM cmsmb";
		try {
			return namedParameterJdbcTemplate.getJdbcOperations().query(sql.toString(), new BeanPropertyRowMapper<Cmsmb>(Cmsmb.class));
		} catch (Exception e) {
			log.error("exception :", e);
			throw new DatabaseException(e.getMessage());
		}
	}

}
